﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace AbacusCustom
{
    public partial class frmCreateReport : Form
    {
        public frmCreateReport()
        {
            InitializeComponent();
        }

        private void get_columns(String tablename)
        {
            Module db_conf = new Module();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=PSCUSTOM;" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT t1.ID,t1.TABLE_TYPE_ID,t1.TABLE_ID,t1.IS_PRIMARY,t1.NAME,t1.LONG_NAME,t1.DATA_TYPE,t1.LENGTH,t1.DESCRIPTION,t2.NAME FROM PS_COLUMN t1 JOIN PS_TABLE t2 ON t1.TABLE_ID = t2.ID WHERE TABLE_ID = '" + tablename + "'";

            SqlDataReader reader = cmd.ExecuteReader();

            dgvColumns.Rows.Clear();

            if (reader != null)
            {
                while (reader.Read())
                {
                    DataGridViewRow row = new DataGridViewRow();
                    row.CreateCells(dgvColumns);
                    row.Cells[0].Value = reader.GetValue(0).ToString();
                    row.Cells[1].Value = reader.GetValue(1).ToString();
                    row.Cells[2].Value = reader.GetValue(2).ToString();
                    row.Cells[3].Value = reader.GetValue(3).ToString();
                    row.Cells[4].Value = reader.GetValue(4).ToString();
                    row.Cells[5].Value = reader.GetValue(5).ToString();
                    row.Cells[6].Value = reader.GetValue(6).ToString();
                    row.Cells[7].Value = reader.GetValue(7).ToString();
                    row.Cells[8].Value = reader.GetValue(8).ToString();
                    row.Cells[9].Value = reader.GetValue(9).ToString();
                    dgvColumns.Rows.Add(row);
                }
            }
            conn.Close();
        }

        private void get_tables(String tabletype)
        {
            Module db_conf = new Module();

            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=PSCUSTOM;" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ID,NAME FROM PS_TABLE WHERE TABLE_TYPE_ID='" + tabletype + "'";

            SqlDataReader reader = cmd.ExecuteReader();
            dgvTables.Rows.Clear();
            while (reader.Read())
            {
               DataGridViewRow row = new DataGridViewRow();
               row.CreateCells(dgvTables);
               row.Cells[0].Value = reader.GetInt32(0).ToString();
               row.Cells[1].Value = reader.GetString(1);

               dgvTables.Rows.Add(row);
            }
            conn.Close();
        }

        private void get_table_types()
        {
            Module db_conf = new Module();
            SqlConnection conn = new SqlConnection();
            conn.ConnectionString =
            "Data Source=" + db_conf.db_host + ";" +
            "Database=PSCUSTOM;" +
            "User ID=" + db_conf.db_user + ";" +
            "Password=" + db_conf.db_password + ";";
            conn.Open();

            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = "SELECT ID,NAME FROM PS_TABLE_TYPE ORDER BY ID ASC";

            SqlDataReader reader = cmd.ExecuteReader();
            dgvTableTypes.Rows.Clear();
            while (reader.Read())
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dgvTableTypes);
                row.Cells[0].Value = reader.GetInt32(0).ToString();
                row.Cells[1].Value = reader.GetString(1);
                dgvTableTypes.Rows.Add(row);
            }
            conn.Close();
        }

        private void frmCreateReport_Load(object sender, EventArgs e)
        {
            //get_databases();
            get_table_types();
            dataGridView1.Rows.Clear();
        }

        private void dgvTableTypes_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_tables(dgvTableTypes.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception)
            {
                
            }
        }

        private void dgvTables_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_columns(dgvTables.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception)
            {
                
            }
        }

        private void dgvColumns_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                DataGridViewRow row = new DataGridViewRow();
                row.CreateCells(dgvColumnList);
                row.Cells[0].Value = dgvColumns.Rows[e.RowIndex].Cells[1].Value.ToString();
                row.Cells[1].Value = dgvColumns.Rows[e.RowIndex].Cells[2].Value.ToString();
                row.Cells[2].Value = dgvColumns.Rows[e.RowIndex].Cells[0].Value.ToString();
                row.Cells[3].Value = dgvColumns.Rows[e.RowIndex].Cells[5].Value.ToString();
                row.Cells[4].Value = dgvColumns.Rows[e.RowIndex].Cells[8].Value.ToString();
                row.Cells[5].Value = dgvColumns.Rows[e.RowIndex].Cells[9].Value.ToString();
                dgvColumnList.Rows.Add(row);

                String cektables="";
                dgvTablesKey.Rows.Clear();

                for (int xx = 0; xx < 100; xx++)
                {
                    Module.tablelist[xx] = "";
                }

                for (int a = 0; a < (dgvColumnList.RowCount - 1); a++)
                {
                    if (cektables.Equals(""))
                    {
                        DataGridViewRow row2 = new DataGridViewRow();
                        row2.CreateCells(dgvTablesKey);
                        row2.Cells[0].Value = dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                        cektables += dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim() + "|";
                        dgvTablesKey.Rows.Add(row2);

                        Module.tablelist[a] = dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                    }
                    else if(!cektables.Contains(dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim()+"|"))
                    {
                        DataGridViewRow row2 = new DataGridViewRow();
                        row2.CreateCells(dgvTablesKey);
                        row2.Cells[0].Value = dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                        cektables += dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim() + "|";
                        dgvTablesKey.Rows.Add(row2);

                        Module.tablelist[a] = dgvColumnList.Rows[a].Cells[5].Value.ToString().Trim();
                    }
                }

            }
            catch (Exception)
            {
            }
        }

        private void dgvColumnList_CellContentDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                dgvColumnList.Rows.RemoveAt(dgvColumnList.CurrentCell.RowIndex);
            }
            catch (Exception)
            {
                
            }
        }

        private void btnCreateReport_Click(object sender, EventArgs e)
        {
            if ((dgvColumnList.RowCount - 1) < 1)
            {
                MessageBox.Show("Column list still empty","Information");
            }
            else if (txtReportName.Text.Equals(""))
            {
                MessageBox.Show("Report Name must be filled", "Information");
            }
            else if (txtReportDescription.Text.Equals(""))
            {
                MessageBox.Show("Report Description must be filled", "Information");
            }
            else
            {
                //insert new report
                Module db_conf = new Module();
                SqlConnection conn = new SqlConnection();
                conn.ConnectionString =
                "Data Source=" + db_conf.db_host + ";" +
                "Database=PSCUSTOM;" +
                "User ID=" + db_conf.db_user + ";" +
                "Password=" + db_conf.db_password + ";"+
                "MultipleActiveResultSets=true";
                conn.Open();

                SqlCommand cmd = conn.CreateCommand();
                cmd.CommandText = "INSERT INTO PS_REPORT(ACCOUNT_ID,NAME,DESCRIPTION) VALUES('" + Module.session_userid + "','"+txtReportName.Text+"','"+txtReportDescription.Text+"')";
                cmd.ExecuteReader();

                SqlCommand cmd2 = conn.CreateCommand();
                cmd2.CommandText = "SELECT SCOPE_IDENTITY();";
                SqlDataReader reader = cmd2.ExecuteReader();
                reader.Read();

                String report_id;
                report_id = reader.GetValue(0).ToString();

                SqlCommand cmd3 = conn.CreateCommand();
                cmd3.CommandText = "";
                
                for (int a = 0; a < (dgvColumnList.RowCount - 1) - 1; a++)
                {
                    cmd3.CommandText += "INSERT INTO PS_REPORT_COLUMN(REPORT_ID,TABLE_TYPE_ID,TABLE_ID,COLUMN_ID,NAME,DESCRIPTION) VALUES('" + report_id + "','" + dgvColumnList.Rows[a].Cells[0].Value.ToString() + "','" + dgvColumnList.Rows[a].Cells[1].Value.ToString() + "','" + dgvColumnList.Rows[a].Cells[2].Value.ToString() + "','" + dgvColumnList.Rows[a].Cells[3].Value.ToString() + "','" + dgvColumnList.Rows[a].Cells[4].Value.ToString() + "'); ";   
                }
                cmd3.ExecuteReader();
                conn.Close();

                MessageBox.Show("New report created", "Information");
            } 
        }

        private void dgvTableTypes_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_tables(dgvTableTypes.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception )
            {

            }
        }

        private void dgvTables_CellContentClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_columns(dgvTables.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception q)
            {
                MessageBox.Show(q.ToString());
            }
        }

        private void dgvTables_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_columns(dgvTables.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception)
            {

            }
        }

        private void dgvTableTypes_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            try
            {
                get_tables(dgvTableTypes.Rows[e.RowIndex].Cells[0].Value.ToString());
            }
            catch (Exception )
            {

            }
        }

        private void btnAddRelation_Click(object sender, EventArgs e)
        {

            /*DataGridViewRow row2 = new DataGridViewRow();
            row2.CreateCells(dataGridView1);
            for (int a = 0; a < (dgvTablesKey.RowCount - 1); a++)
            {
                DataGridViewComboBoxCell cbCell = (DataGridViewComboBoxCell)row2.Cells[0];
                cbCell.Items.Add(dgvTablesKey.Rows[a].Cells[0].Value.ToString().Trim());
            }

            DataGridViewComboBoxCell cbCell2 = (DataGridViewComboBoxCell)row2.Cells[3];
            cbCell2.Items.Add("INNER JOIN");
            cbCell2.Items.Add("LEFT JOIN");
            cbCell2.Items.Add("RIGHT JOIN");
            cbCell2.Items.Add("OUTER JOIN");

            for (int a = 0; a < (dgvTablesKey.RowCount - 1); a++)
            {
                DataGridViewComboBoxCell cbCell = (DataGridViewComboBoxCell)row2.Cells[3];
                cbCell.Items.Add(dgvTablesKey.Rows[a].Cells[0].Value.ToString().Trim());
            }
            dataGridView1.Rows.Add(row2);*/

            DataGridViewRow row2 = new DataGridViewRow();

            dataGridView1.Rows.Add(row2);

        }

        public void setvalue(String asd)
        {
            dataGridView1.Rows[Module.selectedrow].Cells[Module.selectedcolumn].Value = asd;
        }

        private void dataGridView1_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            if (e.ColumnIndex == 0 || e.ColumnIndex == 3)
            {
                TableList asd = new TableList(this);
                Module.selectedrow = e.RowIndex;
                Module.selectedcolumn = e.ColumnIndex;
                asd.ShowDialog();
            }
            else if (e.ColumnIndex == 1 || e.ColumnIndex == 2)
            {
                ColumnList asd = new ColumnList(this,e.ColumnIndex.ToString());
                Module.selectedrow = e.RowIndex;
                Module.selectedcolumn = e.ColumnIndex;
                asd.ShowDialog();
            }
        }
    }
}
